package com.aischool.service;

import java.util.ArrayList;
import java.util.List;

import com.aischool.params.AiTeacherParams;
import com.aischool.utils.RegexUtil;
import com.aischool.utils.StateEnum;
import com.aischool.utils.StringUtil;
import com.jfinal.aop.Inject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.ehcache.CacheKit;

public class AiTeacherService {
	
	public final String CK_CacheName = "CacheTeachers";
	public final String CK_AiTeacher_UID_Pre = "CK_AiTeacher_UID_";

	@Inject 
	SysUserService sysUserService;
	
	/**
	 * 获取分页数据
	 * @param params
	 * @return
	 */
	public Page<Record> getPage(AiTeacherParams params){
		String sql_select = "SELECT a.*, b.user_img, c.post_name, d.state_name ";
		String sql_from   =	"FROM ai_teacher a " + 
				"INNER JOIN sys_user b ON b.user_id = a.user_id " + 
				"INNER JOIN ai_post c ON c.post_id = a.post_id " + 
				"INNER JOIN ai_stu_state d ON d.state_id = a.teacher_stateid ";
		String sql_where  =	"WHERE a.teacher_stateid >= 0 ";
		String sql_order = " ORDER BY a.teacher_id DESC";
		
		List<Object> listObj = new ArrayList<Object>();
		if (params.getState() != StateEnum.ALL.getState()) {
			sql_where =	"WHERE a.teacher_stateid = ?";
			listObj.add(params.getState());
		}
		
		if (params.getOrgId() > 0) {
			sql_where += " AND a.org_id = ? ";
			listObj.add(params.getOrgId());
		}
		
		if (params.getClassId() > 0) {
			sql_where += " AND a.class_id = ? ";
			listObj.add(params.getClassId());
		}
		
		if (params.getRoleId() > 0) {
			sql_from += "INNER JOIN (SELECT user_id FROM sys_user_role WHERE app_id = ? AND role_id = ?) AS e ON e.user_id = a.user_id ";
			listObj.add(0, params.getRoleId());
			listObj.add(0, params.getAppId());
			
			if (params.getRoleId() == 3) {
				sql_select += ", f.class_name ";
				sql_from += "INNER JOIN ai_class f ON f.class_id = a.class_id ";
			}
		}
		
		if (StrKit.notBlank(params.getKeywords())) {
			sql_where += " AND a.teacher_name LIKE ? ";
			listObj.add("%" + params.getKeywords() + "%");
		}
		
		if (params.getAll()) {
			List<Record> list = Db.find(sql_select + sql_from + sql_where + sql_order, listObj.toArray());
			
			return new Page<Record>(list, params.getPageNo(), params.getPageSize(), 1, list.size());
		}else
			return Db.paginate(params.getPageNo(), params.getPageSize(), sql_select, sql_from + sql_where + sql_order, listObj.toArray());
	}
	
	public Record getOne(int user_id) {
		return Db.findFirstByCache(CK_CacheName, CK_AiTeacher_UID_Pre + user_id, 
				"SELECT a.*, b.org_name, c.class_name " + 
				"FROM ai_teacher a " + 
				"INNER JOIN ai_org b ON b.org_id = a.org_id " + 
				"LEFT JOIN ai_class c ON c.class_id = a.class_id "
				+ "WHERE a.user_id = ? ", user_id);
	}
	
	public Record getOneById(int teacher_id) {
		return Db.findFirst("SELECT a.* FROM ai_teacher a WHERE a.teacher_id = ? ", teacher_id);
	}
		
	public List<Record> getTeachers(int class_id){
		return Db.find("SELECT a.*, b.user_img, c.post_name "
				+ "FROM ai_teacher a "
				+ "INNER JOIN sys_user b ON b.user_id = a.user_id "
				+ "INNER JOIN ai_post c ON c.post_id = a.post_id "
				+ "WHERE a.teacher_stateid = 1 AND a.class_id = ?", class_id);
	}
	
	public boolean updateInfo(int user_id, String teacher_mobile, String teacher_name, int state) {
		int ir = Db.update("UPDATE ai_teacher SET teacher_mobile = ?, teacher_name = ?, teacher_stateid =? "
				+ "WHERE user_id = ?", teacher_mobile, teacher_name, state, user_id);
		if (ir>0) {
			this.removeCacheByUserId(user_id);
		}
		return ir>0;
	}

	public boolean del(int teacher_id) {
		Record rd = getOneById(teacher_id);
		if (rd != null) {
			int ir = Db.delete("DELETE FROM ai_teacher WHERE teacher_id = ?", teacher_id);
			if (ir > 0) {
				sysUserService.delUser(rd.getInt("user_id"));
				
				removeCacheByUserId(rd.getInt("user_id"));
			}
			return ir > 0;
		}
		return false;
	}
	
	public boolean delForOrg(int org_id, int teacher_id) {
		Record rd = getOneById(teacher_id);
		if (rd != null) {
			int ir = Db.delete("DELETE FROM ai_teacher WHERE org_id = ? AND teacher_id = ?", org_id, teacher_id);
			if (ir > 0) {
				sysUserService.delUser(rd.getInt("user_id"));
				sysUserService.removeCache(rd.getInt("user_id"));
				removeCacheByUserId(rd.getInt("user_id"));
			}
			return ir > 0;
		}
		return false;
	}
	
	public boolean delMore(String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			List<Record> listrd = Db.find("SELECT a.user_id FROM ai_teacher a INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt ON a.teacher_id = CAST(tt.ids AS INTEGER)", StringUtil.trim(ids, ','));
			String uids = "";
			for(Record rdu : listrd) {
				uids += rdu.getInt("user_id") + ",";
			}
			int ir = Db.delete("DELETE FROM ai_teacher a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.teacher_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','));
			if (ir > 0 && uids.length()>0) {
				sysUserService.delMore(StringUtil.trim(uids, ','));
			}
			if (ir>0) {
				removeCache();
			}
			return ir > 0;
		}
		
		return false;
	}
	
	public boolean delMoreForOrg(int org_id, String ids) {
		if (StrKit.isBlank(ids)) {
			return false;
		}
		if (RegexUtil.isSqlIds(ids)) {
			List<Record> listrd = Db.find("SELECT a.user_id FROM ai_teacher a INNER JOIN (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt ON a.teacher_id = CAST(tt.ids AS INTEGER)", StringUtil.trim(ids, ','));
			String uids = "";
			for(Record rdu : listrd) {
				uids += rdu.getInt("user_id") + ",";
			}
			int ir = Db.delete("DELETE FROM ai_teacher a " + 
					"USING (SELECT regexp_split_to_table(COALESCE(?,''),E',') AS ids) AS tt " +
					"WHERE a.org_id = ? AND a.teacher_id = CAST(tt.ids AS INTEGER)", 
					StringUtil.trim(ids, ','), org_id);
			if (ir > 0 && uids.length()>0) {
				sysUserService.delMore(StringUtil.trim(uids, ','));
			}
			if (ir>0) {
				removeCache();
			}
			return ir > 0;
		}
		
		return false;
	}
	
	public void removeCacheByClassId(int class_id) {
		List<Record> list = getTeachers(class_id);
		if (list != null) {
			for(int i=0; i<list.size(); i++) {
				removeCacheByUserId(list.get(i).getInt("user_id"));
			}
		}
		
	}

	public void removeCacheByUserId(int user_id) {
		CacheKit.remove(CK_CacheName, CK_AiTeacher_UID_Pre + user_id); 
	}

	public void removeCache() {
		CacheKit.removeAll(CK_CacheName);
	}
}
